﻿CREATE PROCEDURE [dbo].[sp_Update_Assignment]
	(
	@AssignmentName varchar(500),
	@ZipIDs VARCHAR(8000),
	@PeriodMonth INT,
	@PeriodYear INT,
	@IsCurrentMonth BIT
	)
AS
	SET NOCOUNT ON
	
	IF CHARINDEX(',', @ZipIDs) = 0
	BEGIN
		IF @IsCurrentMonth = 1
			UPDATE dbo.ZipInfo SET AgentName = @AssignmentName	where ZipID = @ZipIDs

		DELETE FROM AssignmentDetail
		WHERE PeriodMonth = @PeriodMonth AND PeriodYear = @PeriodYear AND ZipID = @ZipIDs

		INSERT INTO AssignmentDetail
		(ZipID, PeriodMonth, PeriodYear, AgentName, AddTS)
		VALUES
		(@ZipIDs, @PeriodMonth, @PeriodYear, @AssignmentName, GETDATE())
	END
	ELSE
	BEGIN
		IF @IsCurrentMonth = 1
			UPDATE dbo.ZipInfo SET  AgentName = @AssignmentName
			where ZipID in (select subcolstr from udf_CharlistToTable(@ZipIDs, ','))

		DELETE FROM AssignmentDetail
		WHERE PeriodMonth = @PeriodMonth AND PeriodYear = @PeriodYear 
		AND ZipID IN (select subcolstr from udf_CharlistToTable(@ZipIDs, ','))

		INSERT INTO AssignmentDetail
		(ZipID, PeriodMonth, PeriodYear, AgentName, AddTS)
		select subcolstr, @PeriodMonth, @PeriodYear, @AssignmentName, GETDATE()
		from udf_CharlistToTable(@ZipIDs, ',')
	END
